﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace IHISCETeim.Web.Export.FXModel
{
    public partial class ReportGetJXSYQ2CSLSBP : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "经销商逾期2期及以上催收落实报表.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "汇总明细";
            }
        }
        protected string LeaseCompany
        {
            get
            {
                return GetRequestValue<string>("LeaseCompany", "");
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            InitializeWorkbook();

           

            //各经销商汇总
            WriteSheet0();


           // hssfworkbook.SetSheetName(0, temptitle);
            DataTable aNList = FXFinanceLeaseController.ReportGetAliasNameJXSYQ2CSLSBP(LeaseCompany);
            string temptitle = "";
            //各个经销商
            string AliasName = "";
            int sheetindex = 2;//从第二个工作表开始
            foreach (DataRow dr in aNList.Rows)
            {
                AliasName = dr["AliasName"].ToString();
                DataTable DT = FXFinanceLeaseController.ReportGetJXSYQ2CSLSBP(AliasName, LeaseCompany);
                ISheet sheet2 = hssfworkbook.CloneSheet(1);
                hssfworkbook.SetSheetName(sheetindex, AliasName);
                //标题
                temptitle = string.Format("经销商{0}逾期2期及以上催收落实：", AliasName);

                WriteSheet(sheet2, DT, temptitle);
                sheet2.ForceFormulaRecalculation = true;
                sheetindex++;
            }

            //明细表汇总
            DataTable allDT = FXFinanceLeaseController.ReportGetJXSYQ2CSLSBP("", LeaseCompany);
             temptitle = string.Format("{0}经销商逾期2期及以上催收落实：", string.IsNullOrEmpty(LeaseCompany) ? "" : "(" + LeaseCompany + ")");

            ISheet sheet0 = hssfworkbook.GetSheetAt(1);
            WriteSheet(sheet0, allDT, temptitle);

            WriteToFile();
        
        }

        private void WriteSheet(ISheet sheet1, DataTable exportDT, string title)
        {
            sheet1.GetRow(0).GetCell(0).SetCellValue(title);
            
            sheet1.GetRow(1).GetCell(7).SetCellValue(IHISCETeim.BLL.FXFinanceLeaseController.GetLastRefresDate());
            int rowindexBase = 3;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["序号"].ToString());
                row1.CreateCell(1).SetCellValue(dr["经销商"].ToString());
                row1.CreateCell(2).SetCellValue(dr["合同号"].ToString());
                row1.CreateCell(3).SetCellValue(dr["用户"].ToString());
                row1.CreateCell(4).SetCellValue(dr["机号"].ToString());
                row1.CreateCell(5).SetCellValue(Convert.ToInt32(dr["总期数"]));
                row1.CreateCell(6).SetCellValue(Convert.ToInt32(dr["应付期数"]));
                row1.CreateCell(7).SetCellValue(Convert.ToInt32(dr["已付期数"]));
                row1.CreateCell(8).SetCellValue(Convert.ToInt32(dr["逾期期数"]));
                row1.CreateCell(9).SetCellValue(Convert.ToDouble(dr["逾期金额"]));
                row1.CreateCell(10).SetCellValue(dr["最近连续几个月未支付租金"].ToString());
                row1.CreateCell(11).SetCellFormula(string.Format("IF(G{0}<=6,IF(I{0}>=1,\"有\",\"\"),\"\")", rowReal + 1));
                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            sheet1.ForceFormulaRecalculation = true;
        }

        private void WriteSheet0()
        {
            DataTableCollection dtc1 = FXFinanceLeaseController.ReportGetJXSYQ2CSLSBPTotal();
            ISheet sheet1 = hssfworkbook.GetSheetAt(0);
            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in dtc1[0].Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["经销商"].ToString());
                row1.CreateCell(1).SetCellValue(Convert.ToInt32( dr["逾期台数"].ToString()));
                row1.CreateCell(2).SetCellValue(Convert.ToDouble( dr["逾期金额"].ToString()));
                row1.CreateCell(3).SetCellValue(Convert.ToInt32(dr["两月以上没付"].ToString()));
                row1.CreateCell(4).SetCellValue(Convert.ToInt32(dr["新客户前6期出现逾期"].ToString()));
                
                rowindex++;
                rowReal = rowindex + rowindexBase;
            }


            HSSFRow rowTotal = (HSSFRow)sheet1.CreateRow(rowReal);
            DataRow drTotal = dtc1[1].Rows[0];
            // row1.CreateCell(0).SetCellValue(1);
            rowTotal.CreateCell(0).SetCellValue("总计:");
            rowTotal.CreateCell(1).SetCellValue(Convert.ToInt32(drTotal["逾期台数"].ToString()));
            rowTotal.CreateCell(2).SetCellValue(Convert.ToDouble(drTotal["逾期金额"].ToString()));
            rowTotal.CreateCell(3).SetCellValue(Convert.ToInt32(drTotal["两月以上没付"].ToString()));
            rowTotal.CreateCell(4).SetCellValue(Convert.ToInt32(drTotal["新客户前6期出现逾期"].ToString()));

            sheet1.ForceFormulaRecalculation = true;
        }
    }
}